﻿using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Services;
using System.Web.Services.Protocols;
using System.Xml.Linq;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Xml;
using System.Drawing;
using System.IO;
using System.Text;

namespace AuctionDataService2
{
    /// <summary>
    /// Summary description for Service1
    /// </summary>
    [WebService(Namespace = "http://tempuri.org/")]
    [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
    [ToolboxItem(false)]
    // To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line. 
    // [System.Web.Script.Services.ScriptService]
    public class Service1 : System.Web.Services.WebService
    {
        private string connectionString = "Data Source=MR-DEMON\\SQLEXPRESS;Initial Catalog=auctions2;Integrated Security=True;";
        private SqlConnection conn = null;
        private DataSet dataset = null;
        private SqlDataAdapter adapter = null;

        private void LogError(Exception ex)
        {
            //TODO
            //should log error
        }

       [WebMethod]
       public DataTable ListUser(string username, string password)
        {
            String command = "SELECT * FROM users WHERE username=@username AND password=@password;";
            DataTable dt = new DataTable();
            try
            {
                conn = new SqlConnection(connectionString);
                conn.Open();
                adapter = new SqlDataAdapter();
                SqlCommand sqlCommand = new SqlCommand(command, conn);
                sqlCommand.Parameters.Add("@username", System.Data.SqlDbType.VarChar, 50).Value = username;
                sqlCommand.Parameters.Add("@password", System.Data.SqlDbType.VarChar, 50).Value = password; 
                adapter.SelectCommand = sqlCommand;
                dataset = new DataSet();
                adapter.Fill(dataset);
                dt = dataset.Tables[0];
            }
            catch (Exception ex)
            {
                LogError(ex);
            }
            finally
            {
                if (conn != null)
                    conn.Close();
            }
            return dt;
        }

       [WebMethod]
       public DataTable ListCategories()
       {
           String command = "SELECT cat_id,cat_name FROM categories";
           DataTable dt = new DataTable();
           try
           {
               conn = new SqlConnection(connectionString);
               conn.Open();
               adapter = new SqlDataAdapter();
               SqlCommand sqlCommand = new SqlCommand(command, conn);
               adapter.SelectCommand = sqlCommand;
               dataset = new DataSet();
               adapter.Fill(dataset);
               dt = dataset.Tables[0];

           }
           catch (Exception ex)
           {
               LogError(ex);
           }
           finally
           {
               if (conn != null)
                   conn.Close();
           }
           return dt;
       }

       //[WebMethod]
       //public String ListCategories()
       //{
       //    String command = "SELECT * FROM categories";
       //    DataTable dt = new DataTable();
       //    XmlDocument doc = new XmlDocument();
       //    XmlElement root = doc.CreateElement("root");
       //    try
       //    {
       //        conn = new SqlConnection(connectionString);
       //        conn.Open();
       //        adapter = new SqlDataAdapter();
       //        SqlCommand sqlCommand = new SqlCommand(command, conn);
       //        adapter.SelectCommand = sqlCommand;
       //        dataset = new DataSet();
       //        adapter.Fill(dataset);
       //        dt = dataset.Tables[0];
       //        doc.AppendChild(root);
       //        foreach (DataRow row in dt.Rows)
       //        {
       //            XmlElement el = (XmlElement)root.AppendChild(doc.CreateElement("obj"));
       //            el.SetAttribute("data", row["cat_id"].ToString());
       //            el.SetAttribute("label", row["cat_name"].ToString());
       //        }
       //    }
       //    catch (Exception ex)
       //    {
       //        Console.WriteLine(ex.Message);
       //        Console.WriteLine(ex.StackTrace);
       //    }
       //    finally
       //    {
       //        if (conn != null)
       //            conn.Close();
       //    }
       //    return doc.InnerXml;
       //}


        [WebMethod]
        public DataTable ListProducts(int category)
        {   
            String command;
            if (category != 0)
                command = "SELECT * FROM products WHERE category = @category;";
            else 
                command = "SELECT * FROM products";
            DataTable dt = new DataTable();
            try
            {
                conn = new SqlConnection(connectionString);
                conn.Open();
                adapter = new SqlDataAdapter();
                SqlCommand sqlCommand = new SqlCommand(command, conn);
                if (category!=0)
                    sqlCommand.Parameters.Add("@category", SqlDbType.Int).Value = category;
                adapter.SelectCommand = sqlCommand;
                dataset = new DataSet();
                adapter.Fill(dataset);
                dt = dataset.Tables[0];
            }
            catch (Exception ex)
            {
                LogError(ex);
            }
            finally
            {
                if (conn != null)
                    conn.Close();
            }
            return dt;
        }

        [WebMethod]
        public DataTable ListWatched(int userId, int category)
        {
            String command;
            if (category != 0)
                command = "SELECT * FROM products WHERE product_id = ANY(SELECT product_id FROM watched WHERE user_id = @user) AND category=@category";
            else
                command = "SELECT * FROM products WHERE product_id = ANY(SELECT product_id FROM watched WHERE user_id = @user);";
            DataTable dt = new DataTable();
            try
            {
                conn = new SqlConnection(connectionString);
                conn.Open();
                adapter = new SqlDataAdapter();
                SqlCommand sqlCommand = new SqlCommand(command, conn);
                sqlCommand.Parameters.Add("@user", SqlDbType.Int).Value = userId;
                if (category!=0)
                    sqlCommand.Parameters.Add("@category", SqlDbType.Int).Value = category;
                adapter.SelectCommand = sqlCommand;
                dataset = new DataSet();
                adapter.Fill(dataset);
                dt = dataset.Tables[0];
            }
            catch (Exception ex)
            {
                LogError(ex);
            }
            finally
            {
                if (conn != null)
                    conn.Close();
            }
            return dt;
        }

        [WebMethod]
        public int WatchProduct(int product_id, int user_id)
        {
            String command = @"INSERT INTO watched (product_id, user_id) VALUES (@product_id, @user_id)";
            SqlCommand sqlCommand;
            try
            {
                conn = new SqlConnection(connectionString);
                conn.Open();
                sqlCommand = new SqlCommand(command, conn);
                sqlCommand.Parameters.Add("@product_id", SqlDbType.Int).Value = product_id;
                sqlCommand.Parameters.Add("@user_id", SqlDbType.Int).Value = user_id;
                sqlCommand.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                LogError(ex);
            }
            finally
            {
                if (conn != null)
                    conn.Close();
            }
            return 0;
        }

        [WebMethod]
        public int UnWatchProduct(int product_id, int user_id)
        {
            String command = @"DELETE FROM watched WHERE product_id = @product_id AND user_id = @user_id";
            SqlCommand sqlCommand;
            try
            {
                conn = new SqlConnection(connectionString);
                conn.Open();
                sqlCommand = new SqlCommand(command, conn);
                sqlCommand.Parameters.Add("@product_id", SqlDbType.Int).Value = product_id;
                sqlCommand.Parameters.Add("@user_id", SqlDbType.Int).Value = user_id;
                sqlCommand.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                LogError(ex);
            }
            finally
            {
                if (conn != null)
                    conn.Close();
            }
            return 0;
        }


        [WebMethod]
        public int IncreaseBid(int product_id, int buyer_id, int new_bid)
        {
            String command = @"UPDATE products SET bid = @bid, buyer_id = @buyer WHERE product_id = @id";
            SqlCommand sqlCommand;
            try
            {
                conn = new SqlConnection(connectionString);
                conn.Open();
                sqlCommand = new SqlCommand(command, conn);
                sqlCommand.Parameters.Add("@bid", System.Data.SqlDbType.VarChar, 50).Value = new_bid;
                sqlCommand.Parameters.Add("@buyer", System.Data.SqlDbType.Int).Value = buyer_id;
                sqlCommand.Parameters.Add("@id", System.Data.SqlDbType.Int).Value = product_id;
                sqlCommand.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                LogError(ex);
            }
            finally
            {
                if (conn != null)
                    conn.Close();
            }
            return 0;
        }

      //  [WebMethod]
        //public string GetAppPath()
        //{
            //return AppDomain.CurrentDomain.BaseDirectory;
           //return HttpContext.Current.ApplicationInstance.Server.MapPath("~/App_Data");
           // return System.Web.Hosting.HostingEnvironment.MapPath(@"~/App_Data");
        //}
        //[WebMethod]
        //public string GetApplicationPath()
        private string GetApplicationPath()
        {
            return HttpContext.Current.Request.Url.Host + ":" + HttpContext.Current.Request.Url.Port.ToString() + HttpContext.Current.Request.ApplicationPath;
        }

        private string GetLocalPath()
        {
            return AppDomain.CurrentDomain.BaseDirectory;
        }

        [WebMethod]
        public int AddProduct(string product_name, int bid, string date, int seller_id, string product_description, int category, byte[] image)
        {
            MemoryStream ms;
            FileStream fs;
            String remotePath;
            try
            {
                ms = new MemoryStream(image);
                fs = new FileStream(GetLocalPath() + "\\images\\" + product_name + ".jpg", FileMode.Create, FileAccess.ReadWrite);
                ms.WriteTo(fs);
                ms.Close();
                fs.Close();
            }
            catch (Exception ex)
            {
                LogError(ex);
            }

            remotePath = "http://" + GetApplicationPath() + "images/" + product_name + ".jpg";
            String command = @"INSERT INTO products (bid, date, product_name, active, seller_id, product_description, category, image) VALUES (@bid, @date, @product_name, @active, @seller_id, @product_description, @category, @image)";
            SqlCommand sqlCommand;
            int result = 0;
            try
            {
                conn = new SqlConnection(connectionString);
                conn.Open();
                sqlCommand = new SqlCommand(command, conn);
                sqlCommand.Parameters.Add("@bid", SqlDbType.Int).Value = bid;
                sqlCommand.Parameters.Add("@date", SqlDbType.VarChar, 50).Value = date;
                sqlCommand.Parameters.Add("@product_name", SqlDbType.VarChar, 50).Value = product_name;
                sqlCommand.Parameters.Add("@active", SqlDbType.Int).Value = 1;
                sqlCommand.Parameters.Add("@seller_id", SqlDbType.Int).Value = seller_id;
                sqlCommand.Parameters.Add("@product_description", SqlDbType.VarChar, 50).Value = product_description;
                sqlCommand.Parameters.Add("@category", SqlDbType.Int).Value = category;
                sqlCommand.Parameters.Add("@image", SqlDbType.VarChar, 50).Value = remotePath;
                result = sqlCommand.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                LogError(ex);
            }
            finally
            {
                if (conn != null)
                    conn.Close();
            }
            return result;
        }

        //[WebMethod]
        //public string GetImage(string image)
        //{
        //    return "http://" + GetApplicationPath() + "images/" + image + ".jpg";
        //}

        [WebMethod]
        public void UpdateWin()
        {
            String command = "UPDATE products SET active=0 WHERE DATEDIFF(dayofyear,GETDATE(),CONVERT(datetime,date))<0";
            String command1 = "SELECT COUNT(*) FROM flags WHERE DATEDIFF(dayofyear,GETDATE(),CONVERT(datetime,lastupdated))<0";
            String command2 = "UPDATE flags SET lastupdated = CONVERT(varchar(50),GETDATE(),101)";
            SqlCommand sqlCommand;
            int result=0;
            try
            {
                conn = new SqlConnection(connectionString);
                conn.Open();
                sqlCommand = new SqlCommand(command1, conn);
                result = (int)sqlCommand.ExecuteScalar();
                if (result == 1)
                {
                    sqlCommand = new SqlCommand(command, conn);
                    result = sqlCommand.ExecuteNonQuery();
                    sqlCommand = new SqlCommand(command2, conn);
                    sqlCommand.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {
                LogError(ex);
            }
            finally
            {
                if (conn != null)
                    conn.Close();
            }
            //return result;            
        }

        [WebMethod]
        public string GetCatTree()
        {
            String command = "SELECT * FROM categories;";
            DataTable dt = new DataTable();
            XmlDocument doc = new XmlDocument();
            XmlElement root = doc.CreateElement("root");
            try
            {
                conn = new SqlConnection(connectionString);
                conn.Open();
                adapter = new SqlDataAdapter();
                adapter.SelectCommand = new SqlCommand(command, conn);
                dataset = new DataSet();
                adapter.Fill(dataset);
                dt = dataset.Tables[0];
                doc.AppendChild(root);
                foreach (DataRow row in dt.Rows)
                {
                    if (row["parent"].ToString() == "")
                    {
                        XmlElement el = (XmlElement)root.AppendChild(doc.CreateElement("node"));
                        el.SetAttribute("cat_id", row["cat_id"].ToString());
                        el.SetAttribute("cat_name", row["cat_name"].ToString());
                        el.SetAttribute("parent", row["parent"].ToString());
                    }
                    else
                    {
                        XmlNodeList nodeList = doc.SelectNodes("/root/node[@cat_id='" + row["parent"].ToString() + "']");
                        XmlElement parent = (XmlElement)nodeList.Item(0);
                        XmlElement el = (XmlElement)parent.AppendChild(doc.CreateElement("node"));
                        el.SetAttribute("cat_id", row["cat_id"].ToString());
                        el.SetAttribute("cat_name", row["cat_name"].ToString());
                        el.SetAttribute("parent", row["parent"].ToString());
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
                Console.WriteLine(ex.StackTrace);
            }
            finally
            {
                if (conn != null)
                    conn.Close();
            }
            return doc.InnerXml;
          }
          

        [WebMethod]
        public int InsertUser(string name, string username, string password, string address, string billing, string email)
        {
            String command = @"INSERT INTO users (name,username,password,address,billing,email) VALUES (@name,@username,@password,@address,@billing,@email)";
            SqlCommand sqlCommand;
            int result = 0;
            try
            {
                conn = new SqlConnection(connectionString);
                conn.Open();
                sqlCommand = new SqlCommand(command, conn);
                sqlCommand.Parameters.Add("@name", SqlDbType.NVarChar, 50).Value = name;
                sqlCommand.Parameters.Add("@username", SqlDbType.NVarChar, 50).Value = username;
                sqlCommand.Parameters.Add("@password", SqlDbType.NVarChar, 50).Value = password;
                sqlCommand.Parameters.Add("@address", SqlDbType.NVarChar, 50).Value = address;
                sqlCommand.Parameters.Add("@billing", SqlDbType.NVarChar, 50).Value = billing;
                sqlCommand.Parameters.Add("@email", SqlDbType.NVarChar, 50).Value = email;
                result = sqlCommand.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                LogError(ex);
            }
            finally
            {
                if (conn != null)
                    conn.Close();
            }
            return result;
        }

    }
}
